﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;
using Utility;
namespace Service
{
    /// <summary>
    /// 功能：HW_School类数据访问
    /// 创建人：Wilson
    /// 创建时间：2013-1-30   
    /// </summary>
    public class HW_SchoolService : IDisposable
    {
        #region 公共
        private System.ComponentModel.Component component = new System.ComponentModel.Component();

        private bool disposed = false;

        ~HW_SchoolService()
        {
            Dispose(false);
        }

        #endregion

        #region IDisposable 成员

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (!this.disposed)
            {

                if (disposing)
                {

                    component.Dispose();
                }

                disposed = true;

            }
        }
        #endregion
		#region##MapperHW_School
        public IList<HW_School> MapperList(SqlDataReader reader)
         {
            IList<HW_School> list = new List<HW_School>();
            HW_School obj=null;
            while (reader.Read())
            {
                obj = new HW_School();
                obj.SchoollCode = (string)reader["SchoollCode"];
                obj.OrgCode = reader["OrgCode"] as string;
                obj.DistrictCode = (string)reader["DistrictCode"];
                obj.DistrictName = reader["DistrictName"] as string;
                obj.Address = reader["Address"] as string;
                obj.Email = reader["Email"] as string;
                obj.LinkMan = reader["LinkMan"] as string;
                obj.Name = reader["Name"] as string;
                obj.MobilePhone = reader["MobilePhone"] as string;
                obj.OfficePhone = reader["OfficePhone"] as string;
                obj.Version = (int)reader["Version"];
                obj.Statute = reader["Statute"] as string;
                obj.CreateDate = reader["CreateDate"] as DateTime?;
                obj.OutDate = reader["OutDate"] as DateTime?;
                obj.SchoolCharacter = reader["SchoolCharacter"] as string;
                obj.SchoolType = reader["SchoolType"] as string;
                obj.Permit = reader["Permit"] as bool?;
                obj.CityTown = reader["CityTown"] as string;
                obj.Remark = reader["Remark"] as string;
                obj.SecCode = reader["SecCode"] as string;
                obj.CityId = (string)reader["CityId"];
                obj.CityName = reader["CityName"] as string; 
                list.Add(obj);
            }
            return list;
         }
        public HW_School Mapper(SqlDataReader reader)
         {
            HW_School obj=null;
            if (reader.Read())
            {
                obj = new HW_School();
                obj.SchoollCode = (string)reader["SchoollCode"];
                obj.OrgCode = reader["OrgCode"] as string;
                obj.DistrictCode = (string)reader["DistrictCode"];
                obj.DistrictName = reader["DistrictName"] as string;
                obj.Address = reader["Address"] as string;
                obj.Email = reader["Email"] as string;
                obj.LinkMan = reader["LinkMan"] as string;
                obj.Name = reader["Name"] as string;
                obj.MobilePhone = reader["MobilePhone"] as string;
                obj.OfficePhone = reader["OfficePhone"] as string;
                obj.Version = (int)reader["Version"];
                obj.Statute = reader["Statute"] as string;
                obj.CreateDate = reader["CreateDate"] as DateTime?;
                obj.OutDate = reader["OutDate"] as DateTime?;
                obj.SchoolCharacter = reader["SchoolCharacter"] as string;
                obj.SchoolType = reader["SchoolType"] as string;
                obj.Permit = reader["Permit"] as bool?;
                obj.CityTown = reader["CityTown"] as string;
                obj.Remark = reader["Remark"] as string;
                obj.SecCode = reader["SecCode"] as string;
                obj.CityId = (string)reader["CityId"];
                obj.CityName = reader["CityName"] as string; 
            }
            return obj;
         }
        #endregion
        #region##通过主键ID得到HW_School
        /// <summary>
        /// 功能：通过ID得到HW_School
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
        public HW_School GetById(string schoollcode)
        {
            string sql = "select * from HW_School where SchoollCode = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",schoollcode)
            };
            
            SqlDataReader reader=SqlHelper.ExecuteReader(WebConn.connString,CommandType.Text,sql,sp);
            return Mapper(reader);
        }
        #endregion
        public DataTable GetByDistrict(int pageIndex,int pageSize,out int count,string DistrictId,string searchString)
        {
            if (string.IsNullOrEmpty(searchString))
            {
                string sql = "select top(@pageSize) * from HW_School where DistrictCode=@DistrictId and SchoollCode not in(select top(@pageSize*@pageIndex) SchoollCode from HW_School where DistrictCode=@DistrictId order by SchoollCode) order by SchoollCode";
                SqlParameter[] sp2 = new SqlParameter[]
            {
                new SqlParameter("@DistrictId",DistrictId),
            };
                SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@DistrictId",DistrictId),
            };
                string sql2 = "select count(SchoollCode) from HW_School where DistrictCode=@DistrictId";
                DataTable dt = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
                object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp);
                count = Convert.ToInt32(o);
                return dt;
            }
            else
            {
                string sql = "select top(@pageSize) * from HW_School where DistrictCode=@DistrictId and name like @searchString and SchoollCode not in(select top(@pageSize*@pageIndex) SchoollCode from HW_School where DistrictCode=@DistrictId and name like @searchString order by SchoollCode) order by SchoollCode";
                SqlParameter[] sp2 = new SqlParameter[]
            {
                new SqlParameter("@DistrictId",DistrictId),
                new SqlParameter("@searchString","%"+searchString+"%"),
            };
                SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@DistrictId",DistrictId),
                new SqlParameter("@searchString","%"+searchString+"%"),
            };
                string sql2 = "select count(SchoollCode) from HW_School where DistrictCode=@DistrictId and name like @searchString";
                DataTable dt = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
                object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp);
                count = Convert.ToInt32(o);
                return dt;
            }
        }
        public DataTable GetMyShcool(string SchoolCode, string districtcode)
        {
            string sql = @";WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@districtcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
            select * from HW_School inner join m where SchoollCode = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@districtcode",districtcode),
                new SqlParameter("@PK",SchoolCode)
            };
            DataTable dt = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
            return dt;
        }
        public DataTable GetMySchool(string year,string orgCode)
        {
            string sql = "select * from HW_School a left join (select * from HW_SchoolUpLoadlog where Upyearcode=@year) c on a.Schoollcode=c.UpSchoolCode  where orgcode=@orgcode";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@year",year),
                new SqlParameter("@orgcode",orgCode),
            };
            DataTable dt = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
            return dt;
        }
        /// <summary>
        /// 区域统计上传学生数
        /// </summary>
        /// <param name="year"></param>
        /// <param name="districtid"></param>
        /// <returns></returns>
        public DataTable GetDistrictSchool(string year, string districtid)
        {
            string sql = @"select * from HW_school a left join HW_schooluploadlog b on a.Schoollcode=b.upschoolcode where a.districtcode=@districtid and b.upyearcode=@year";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@year",year),
                new SqlParameter("@districtid",districtid),
            };
            DataTable dt = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
            return dt;
        }
        public DataTable GetSchoolByOrgCode(int pageIndex,int pageSize,out int count,string orgCode)
        {
            string sql = @"select top(@pageSize) a.SchoollCode,a.permit,MobilePhone,OfficePhone,LinkMan,Email,Address,Statute,SchoolType,OrgName,a.name schoolname,cityname,districtname from HW_School a inner join HW_org b on a.orgcode=b.orgcode where a.OrgCode=@orgCode and Schoollcode not in(
                           select top(@pageSize*@pageIndex) Schoollcode from HW_School where OrgCode=@orgCode order by Schoollcode desc) order by Schoollcode desc  ";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@orgCode",orgCode)
            };
            string sql2 = @"select count(*) from HW_School where OrgCode=@orgCode ";
            SqlParameter[] sp2 = new SqlParameter[]
            {
                new SqlParameter("@orgCode",orgCode)
            };
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp);
            object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp2);
            count = Convert.ToInt32(o);
            return ds.Tables[0];
        }
        public DataTable GetSchoolBySchoolCode(int pageIndex, int pageSize, out int count, string SchoolCode)
        {
            string sql = @"select top(@pageSize) a.SchoollCode,a.permit,MobilePhone,OfficePhone,LinkMan,Email,Address,Statute,SchoolType,OrgName,a.name schoolname,cityname,districtname  from HW_School a inner join HW_org b on a.orgcode=b.orgcode where a.SchoollCode=@SchoolCode and Schoollcode not in(
                           select top(@pageSize*@pageIndex) Schoollcode from HW_School where SchoollCode=@SchoolCode order by Schoollcode desc) order by Schoollcode desc  ";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@SchoolCode",SchoolCode)
            };
            string sql2 = @"select count(*) from HW_School where SchoollCode=@SchoolCode ";
            SqlParameter[] sp2 = new SqlParameter[]
            {
                new SqlParameter("@SchoolCode",SchoolCode)
            };
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp);
            object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp2);
            count = Convert.ToInt32(o);
            return ds.Tables[0];
        }
        public IList<HW_School> GetAPISchool(string district)
        {
            string sql = "select * from HW_School where SchoollCode Like @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",district+"%")
            };

            SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString, CommandType.Text, sql, sp);
            return MapperList(reader);
        }
		#region##通过主键ID删除HW_School
        /// <summary>
        /// 功能：通过主键ID删除HW_School
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
		public int DeleteById(string schoollcode)
		{
			int i = 0;
			string sql = "delete from HW_School where SchoollCode = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",50)
            };
            i= SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
			return i;
		}
        /// <summary>
        /// 功能：通过主键ID删除HW_School
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
		public int DeleteById(SqlTransaction transaction,string schoollcode)
		{
			int i = 0;
			string sql = "delete from HW_School where SchoollCode = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",schoollcode)
            };
            i= SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
			return i;
		}
		#endregion			
		
        #region##添加HW_School
        /// <summary>
        /// 功能：添加HW_School
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int Add(HW_School hw_school)
        {
            int result = 0;
            string sql = "insert into HW_School(SchoollCode,OrgCode,DistrictCode,Address,Email,LinkMan,Name,MobilePhone,OfficePhone,Version,Statute,CreateDate,OutDate,SchoolCharacter,SchoolType,Permit,CityTown,Remark,SecCode,CityId) values(@SchoollCode,@OrgCode,@DistrictCode,@Address,@Email,@LinkMan,@Name,@MobilePhone,@OfficePhone,@Version,@Statute,@CreateDate,@OutDate,@SchoolCharacter,@SchoolType,@Permit,@CityTown,@Remark,@SecCode,@CityId)";			
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@SchoollCode",hw_school.SchoollCode),
				new SqlParameter("@OrgCode",hw_school.OrgCode),
				new SqlParameter("@DistrictCode",hw_school.DistrictCode),
				new SqlParameter("@Address",hw_school.Address),
				new SqlParameter("@Email",hw_school.Email),
				new SqlParameter("@LinkMan",hw_school.LinkMan),
				new SqlParameter("@Name",hw_school.Name),
				new SqlParameter("@MobilePhone",hw_school.MobilePhone),
				new SqlParameter("@OfficePhone",hw_school.OfficePhone),
				new SqlParameter("@Version",hw_school.Version),
				new SqlParameter("@Statute",hw_school.Statute),
				new SqlParameter("@CreateDate",hw_school.CreateDate),
				new SqlParameter("@OutDate",hw_school.OutDate),
				new SqlParameter("@SchoolCharacter",hw_school.SchoolCharacter),
				new SqlParameter("@SchoolType",hw_school.SchoolType),
				new SqlParameter("@Permit",hw_school.Permit),
				new SqlParameter("@CityTown",hw_school.CityTown),
				new SqlParameter("@Remark",hw_school.Remark),
				new SqlParameter("@SecCode",hw_school.SecCode),
				new SqlParameter("@CityId",hw_school.CityId)
            };
            result = SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
            return result;
        }
         /// <summary>
        /// 功能：添加HW_School
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int Add(SqlTransaction transaction,HW_School hw_school)
        {
            int result = 0;
            string sql = "insert into HW_School(SchoollCode,OrgCode,DistrictCode,Address,Email,LinkMan,Name,MobilePhone,OfficePhone,Version,Statute,CreateDate,OutDate,SchoolCharacter,SchoolType,Permit,CityTown,Remark,SecCode,CityId) values(@SchoollCode,@OrgCode,@DistrictCode,@Address,@Email,@LinkMan,@Name,@MobilePhone,@OfficePhone,@Version,@Statute,@CreateDate,@OutDate,@SchoolCharacter,@SchoolType,@Permit,@CityTown,@Remark,@SecCode,@CityId)";			
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@SchoollCode",hw_school.SchoollCode),
				new SqlParameter("@OrgCode",hw_school.OrgCode),
				new SqlParameter("@DistrictCode",hw_school.DistrictCode),
				new SqlParameter("@Address",hw_school.Address),
				new SqlParameter("@Email",hw_school.Email),
				new SqlParameter("@LinkMan",hw_school.LinkMan),
				new SqlParameter("@Name",hw_school.Name),
				new SqlParameter("@MobilePhone",hw_school.MobilePhone),
				new SqlParameter("@OfficePhone",hw_school.OfficePhone),
				new SqlParameter("@Version",hw_school.Version),
				new SqlParameter("@Statute",hw_school.Statute),
				new SqlParameter("@CreateDate",hw_school.CreateDate),
				new SqlParameter("@OutDate",hw_school.OutDate),
				new SqlParameter("@SchoolCharacter",hw_school.SchoolCharacter),
				new SqlParameter("@SchoolType",hw_school.SchoolType),
				new SqlParameter("@Permit",hw_school.Permit),
				new SqlParameter("@CityTown",hw_school.CityTown),
				new SqlParameter("@Remark",hw_school.Remark),
				new SqlParameter("@SecCode",hw_school.SecCode),
				new SqlParameter("@CityId",hw_school.CityId)
            };
            result = SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
            return result;
        }
        #endregion
		
		#region##得到HW_School集合
		/// <summary>
        /// 功能：得到HW_School集合
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>       
        /// <returns></returns>
		public IList<HW_School> GetList()
		{
			string sql = "select * from HW_School";			
			SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString,CommandType.Text,sql);
            return MapperList(reader);
		}
		#endregion
		
		#region##修改HW_School
        public int Update(string schoolcode,string filed, string value)
        {
            string sql = "update HW_School set " + filed + "=@svalue where schoollcode=@schoolcode";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@svalue",value),
                new SqlParameter("@schoolcode",schoolcode)
            };
            int i = SqlHelper.ExecuteNonQuery(Utility.WebConn.connString, CommandType.Text, sql, sp);
            return i;
        }
        public int ChangeState(string schoolCode, string state,SqlTransaction trans)
        {
            string sql = @"update HW_School set Statute=@state where Schoollcode=@schoolCode";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@state",state),
                new SqlParameter("@schoolCode",schoolCode)
            };
            int i = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sql, sp);
            return i;
        }
        public int ChangePermit(string schoolCode, bool permit, SqlTransaction trans)
        {
            string sql = @"update HW_School set permit=@permit where Schoollcode=@schoolCode";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@permit",permit),
                new SqlParameter("@schoolCode",schoolCode)
            };
            int i = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sql, sp);
            return i;
        }
        public int ChangePermit(string schoolCode, bool permit)
        {
            string sql = @"update HW_School set permit=@permit where Schoollcode=@schoolCode";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@permit",permit),
                new SqlParameter("@schoolCode",schoolCode)
            };
            int i = SqlHelper.ExecuteNonQuery(Utility.WebConn.connString, CommandType.Text, sql, sp);
            return i;
        }
        /// <summary>
        /// 功能：修改HW_School
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int ModHW_School(HW_School hw_school)
        {
            int result = 0;
            string sql = "update HW_School set SchoollCode = @SchoollCode,OrgCode = @OrgCode,DistrictCode = @DistrictCode,Address = @Address,Email = @Email,LinkMan = @LinkMan,Name = @Name,MobilePhone = @MobilePhone,OfficePhone = @OfficePhone,Version = @Version,Statute = @Statute,CreateDate = @CreateDate,OutDate = @OutDate,SchoolCharacter = @SchoolCharacter,SchoolType = @SchoolType,Permit = @Permit,CityTown = @CityTown,Remark = @Remark,SecCode = @SecCode,CityId = @CityId where SchoollCode = @SchoollCode";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@SchoollCode",hw_school.SchoollCode),
				new SqlParameter("@OrgCode",hw_school.OrgCode),
				new SqlParameter("@DistrictCode",hw_school.DistrictCode),
				new SqlParameter("@Address",hw_school.Address),
				new SqlParameter("@Email",hw_school.Email),
				new SqlParameter("@LinkMan",hw_school.LinkMan),
				new SqlParameter("@Name",hw_school.Name),
				new SqlParameter("@MobilePhone",hw_school.MobilePhone),
				new SqlParameter("@OfficePhone",hw_school.OfficePhone),
				new SqlParameter("@Version",hw_school.Version),
				new SqlParameter("@Statute",hw_school.Statute),
				new SqlParameter("@CreateDate",hw_school.CreateDate),
				new SqlParameter("@OutDate",hw_school.OutDate),
				new SqlParameter("@SchoolCharacter",hw_school.SchoolCharacter),
				new SqlParameter("@SchoolType",hw_school.SchoolType),
				new SqlParameter("@Permit",hw_school.Permit),
				new SqlParameter("@CityTown",hw_school.CityTown),
				new SqlParameter("@Remark",hw_school.Remark),
				new SqlParameter("@SecCode",hw_school.SecCode),
				new SqlParameter("@CityId",hw_school.CityId)
            };
            result = SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
            return result;
        }
         /// <summary>
        /// 功能：修改HW_School
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int ModHW_School(SqlTransaction transaction,HW_School hw_school)
        {
            int result = 0;
            string sql = "update HW_School set SchoollCode = @SchoollCode,OrgCode = @OrgCode,DistrictCode = @DistrictCode,Address = @Address,Email = @Email,LinkMan = @LinkMan,Name = @Name,MobilePhone = @MobilePhone,OfficePhone = @OfficePhone,Version = @Version,Statute = @Statute,CreateDate = @CreateDate,OutDate = @OutDate,SchoolCharacter = @SchoolCharacter,SchoolType = @SchoolType,Permit = @Permit,CityTown = @CityTown,Remark = @Remark,SecCode = @SecCode,CityId = @CityId where SchoollCode = @SchoollCode";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@SchoollCode",hw_school.SchoollCode),
				new SqlParameter("@OrgCode",hw_school.OrgCode),
				new SqlParameter("@DistrictCode",hw_school.DistrictCode),
				new SqlParameter("@Address",hw_school.Address),
				new SqlParameter("@Email",hw_school.Email),
				new SqlParameter("@LinkMan",hw_school.LinkMan),
				new SqlParameter("@Name",hw_school.Name),
				new SqlParameter("@MobilePhone",hw_school.MobilePhone),
				new SqlParameter("@OfficePhone",hw_school.OfficePhone),
				new SqlParameter("@Version",hw_school.Version),
				new SqlParameter("@Statute",hw_school.Statute),
				new SqlParameter("@CreateDate",hw_school.CreateDate),
				new SqlParameter("@OutDate",hw_school.OutDate),
				new SqlParameter("@SchoolCharacter",hw_school.SchoolCharacter),
				new SqlParameter("@SchoolType",hw_school.SchoolType),
				new SqlParameter("@Permit",hw_school.Permit),
				new SqlParameter("@CityTown",hw_school.CityTown),
				new SqlParameter("@Remark",hw_school.Remark),
				new SqlParameter("@SecCode",hw_school.SecCode),
				new SqlParameter("@CityId",hw_school.CityId)
            };
            result = SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
            return result;
        }
        public int Permit(string schoolCode)
        {
            string sql = @"update HW_School set Permit='true' where Schoollcode=@schoolCode";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@schoolCode",schoolCode)
            };
            int i = SqlHelper.ExecuteNonQuery(Utility.WebConn.connString, CommandType.Text, sql, sp);
            return i;
        }
        #endregion
		
		#region##统计HW_School
		/// <summary>
        /// 修改HW_School
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
		public int SelectCount()
		{
			int i = 0;
            string sql = "select count(*) from HW_School";
            i = (int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql);
            return i;
		}
		/// <summary>
        /// 修改HW_School
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int SelectCountById()
        {
            int i=0;
            string sql="select count(SchoollCode) from HW_School";
            i = (int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql);
            return i;
        }
		#endregion
        #region##自定义HW_School
        public DataTable GetSchoolDetail(int pageIndex, int pageSize, out int count,string district)
        {
            string sql = @"
                            ;WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@districtcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY a.SchoollCode DESC) AS rowNum, a.SchoollCode,a.permit,MobilePhone,OfficePhone,LinkMan,Email,Address,Statute,SchoolType,OrgName,a.name schoolname,cityname,c.name districtname,xx_username
FROM HW_School a inner join m c  ON a.DistrictCode=c.DistrictCode Left JOIN dbo.HW_Org b ON a.OrgCode=b.OrgCode left join (select * from hw_school_user) sc on a.schoollcode=sc.schoollcode
where Statute='T') AS t WHERE rowNum > @pageSize*@pageIndex AND rowNum <= @pageSize*(@pageIndex+1)";
                //SELECT TOP (@pageSize) a.SchoollCode,a.permit,MobilePhone,OfficePhone,LinkMan,Email,Address,Statute,SchoolType,OrgName,a.name schoolname,cityname,c.name districtname FROM dbo.HW_School a inner JOIN m c ON a.DistrictCode=c.DistrictCode Left JOIN dbo.HW_Org b ON a.OrgCode=b.OrgCode 
                //where Statute='T' and a.SchoollCode not in (select top
                //            (@pageSize*(@pageIndex)) SchoollCode from HW_School x inner join m on x.districtcode=m.districtcode where Statute='T' ORDER BY SchoollCode) ORDER BY a.SchoollCode";
            SqlParameter[] sp = new SqlParameter[]
            {   new SqlParameter("@districtcode",district),
                new SqlParameter("@pageSize",pageSize),
				new SqlParameter("@pageIndex",pageIndex),
            };
            string sql2 = @" ;WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@districtcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
    select count(SchoollCode) from dbo.HW_School a inner join m c on a.DistrictCode=c.DistrictCode where Statute='T';
";          SqlParameter[] sp2 = new SqlParameter[]
            {       
				new SqlParameter("@districtcode",district),
            };
            count =Convert.ToInt32(SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp2));
            return SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
        }
        public DataTable Search(int pageIndex,int pageSize,out int count, string district,string searchString)
        {
            string sql = @" ;WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@districtcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
             SELECT top(@pageSize) a.SchoollCode,a.permit,MobilePhone,OfficePhone,LinkMan,Email,Address,Statute,SchoolType,OrgName,a.name schoolname,cityname,c.name districtname
                FROM dbo.HW_School a inner JOIN m c ON a.DistrictCode=c.DistrictCode left JOIN dbo.HW_Org b ON a.OrgCode=b.OrgCode 
                where
               a.name like @searchString and a.schoollcode not in(select top(@pageSize*@pageIndex) schoollcode from HW_School x inner join m on x.districtcode=m.districtcode where a.name like @searchString order by schoollcode) ORDER BY a.SchoollCode";
            SqlParameter[] sp = new SqlParameter[]
            {   
                new SqlParameter("@districtcode",district),
                new SqlParameter("@pageSize",pageSize),

				new SqlParameter("@pageIndex",pageIndex),
				new SqlParameter("@searchString","%"+searchString+"%"),
            };
            string sql2 = @" ;WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@districtcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
            select count(*) from HW_School a inner join m on a.DistrictCode=m.DistrictCode where a.name like @searchString";
            SqlParameter[] sp2 = new SqlParameter[]
            {   
                new SqlParameter("@districtcode",district),
				new SqlParameter("@searchString","%"+searchString+"%"),
            };
            object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp);
            count = Convert.ToInt32(o);
            return SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
        }
        #endregion
                
    }
}
